﻿
namespace SistemaLogistica.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using SistemaLogistica.Business.Entities;

    public class NotaIngresoData : BaseDataAccess
    {
        private NotaIngresoData() { }

        private readonly static NotaIngresoData instancia = new NotaIngresoData();

        public static NotaIngresoData Instancia
        {
            get { return instancia; }
        }

        public List<NotaIngreso> Listar()
        {
            List<NotaIngreso> notaIngresos = new List<NotaIngreso>();

            DbCommand command = db.GetStoredProcCommand("GetAllFromNotaIngreso");
            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    NotaIngreso notaIngreso = new NotaIngreso
                    {
                        IdNotaIngreso = dr.GetInt32(dr.GetOrdinal("IdNotaIngreso")),
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        Estado = dr.GetInt32(dr.GetOrdinal("Estado")),
                        Fecha = dr.GetDateTime(dr.GetOrdinal("FechaIngreso")),
                        Empleados = EmpleadoData.Instancia.ObtenerPorID(dr.GetInt16(dr.GetOrdinal("IDEmpleado"))),
                        Factura = FacturaData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IDFactura"))),
                        Detalle = DetalleNotaIngresoData.Instancia.ListarPorNotaIngreso(dr.GetInt32(dr.GetOrdinal("IdNotaIngreso")))
                    };
                    notaIngresos.Add(notaIngreso);
                }
            }
            return notaIngresos;
        }

        public List<NotaIngreso> Buscar(DateTime desde, DateTime hasta)
        {
            List<NotaIngreso> notaIngresos = new List<NotaIngreso>();

            DbCommand command = db.GetStoredProcCommand("GetNotaIngresoByFecha");
            db.AddInParameter(command, "desde", DbType.Date, desde);
            db.AddInParameter(command, "hasta", DbType.Date, hasta);
            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    NotaIngreso notaIngreso = new NotaIngreso
                    {
                        IdNotaIngreso = dr.GetInt32(dr.GetOrdinal("IdNotaIngreso")),
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        Estado = dr.GetInt32(dr.GetOrdinal("Estado")),
                        Fecha = dr.GetDateTime(dr.GetOrdinal("FechaIngreso")),
                        Empleados = EmpleadoData.Instancia.ObtenerPorID(dr.GetInt16(dr.GetOrdinal("IDEmpleado"))),
                        Factura = FacturaData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IDFactura"))),
                        Detalle = DetalleNotaIngresoData.Instancia.ListarPorNotaIngreso(dr.GetInt32(dr.GetOrdinal("IdNotaIngreso")))
                    };

                    notaIngresos.Add(notaIngreso);
                }
            }
            return notaIngresos;
        }

        public NotaIngreso ObtenerPorID(int idNotaIngreso)
        {
            NotaIngreso notaIngreso = null;
            
            DbCommand command = db.GetStoredProcCommand("GetNotaIngresoByIdNotaIngreso");
            db.AddInParameter(command, "idNotaIngreso", DbType.Int32, idNotaIngreso);
            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    notaIngreso = new NotaIngreso
                    {
                        IdNotaIngreso = dr.GetInt32(dr.GetOrdinal("IdNotaIngreso")),
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        Estado = dr.GetInt32(dr.GetOrdinal("Estado")),
                        Fecha = dr.GetDateTime(dr.GetOrdinal("FechaIngreso")),
                        Empleados = EmpleadoData.Instancia.ObtenerPorID(dr.GetInt16(dr.GetOrdinal("IDEmpleado"))),
                        Factura = FacturaData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IDFactura"))),
                        Detalle = DetalleNotaIngresoData.Instancia.ListarPorNotaIngreso(dr.GetInt32(dr.GetOrdinal("IdNotaIngreso")))
                    };
                }
            }
            return notaIngreso;
        }

        public int Registrar(NotaIngreso notaIngreso)
        {
            int idNotaIngreso = 0;
            using (DbConnection conection = db.CreateConnection())
            {
                conection.Open();
                using (DbTransaction transaction = conection.BeginTransaction())
                {
                    try
                    {
                        DbCommand cmdNotaIngreso=db.GetStoredProcCommand("InsertNotaIngreso");

                        db.AddInParameter(cmdNotaIngreso, "@descripcion",DbType.String,notaIngreso.Descripcion);
                        db.AddInParameter(cmdNotaIngreso, "@idEmpleado", DbType.Int32, notaIngreso.Empleados.IdEmpleado);
                        db.AddInParameter(cmdNotaIngreso, "@fechaIngreso", DbType.DateTime, notaIngreso.Fecha);
                        db.AddInParameter(cmdNotaIngreso, "@idFactura", DbType.Int32, notaIngreso.Factura.IdFactura);
                        db.AddInParameter(cmdNotaIngreso, "@estado", DbType.Int32, 1);
                        db.AddOutParameter(cmdNotaIngreso, "@idnotaIngreso", DbType.Int32,10);
                        db.ExecuteNonQuery(cmdNotaIngreso, transaction);
                        idNotaIngreso = Convert.ToInt32(cmdNotaIngreso.Parameters["@idNotaIngreso"].Value);

                        DbCommand cmdDetalle =db.GetStoredProcCommand("InsertDetalleNotaIngreso");
                        db.AddInParameter(cmdDetalle, "@cantidad", DbType.Decimal);
                        db.AddInParameter(cmdDetalle, "@precio", DbType.Currency);
                        db.AddInParameter(cmdDetalle, "@idBien", DbType.Int32);
                        db.AddInParameter(cmdDetalle, "@idNotaIngreso", DbType.Int32);
                     

                        foreach (DetalleNotaIngreso d in notaIngreso.Detalle)
                        {
                            db.SetParameterValue(cmdDetalle,"@cantidad",d.Cantidad);
                            db.SetParameterValue(cmdDetalle, "@precio", d.Precio);
                            db.SetParameterValue(cmdDetalle, "@idBien", d.Bien.IdBien);
                            db.SetParameterValue(cmdDetalle, "@idNotaIngreso", idNotaIngreso);
                            db.ExecuteNonQuery(cmdDetalle,transaction);
                        }
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
            return idNotaIngreso;
        }

        public void Modificar(NotaIngreso notaIngreso)
        {
            using (DbConnection conection = db.CreateConnection())
            {
                conection.Open();
                using (DbTransaction transaction = conection.BeginTransaction())
                {
                    try
                    {
                        DbCommand cmdNotaIngreso = db.GetStoredProcCommand("UpdateNotaIngreso");

                        db.AddInParameter(cmdNotaIngreso,"@descripcion", DbType.String,notaIngreso.Descripcion);
                        db.AddInParameter(cmdNotaIngreso, "@idEmpleado", DbType.Int32, notaIngreso.Empleados.IdEmpleado);
                        db.AddInParameter(cmdNotaIngreso, "@fechaIngreso", DbType.DateTime, notaIngreso.Fecha);
                        db.AddInParameter(cmdNotaIngreso, "@idFactura", DbType.Int32, notaIngreso.Factura.IdFactura);
                        db.AddInParameter(cmdNotaIngreso, "@estado", DbType.Int32, 1);//borrar
                        db.AddInParameter(cmdNotaIngreso, "@idnotaIngreso", DbType.Int32, notaIngreso.IdNotaIngreso);
                        db.ExecuteNonQuery(cmdNotaIngreso, transaction);

                        DbCommand cmdDeleteDetalle= db.GetStoredProcCommand("DeleteDetalleNotaIngresoByNotaIngreso");

                        db.AddInParameter(cmdDeleteDetalle,"@idNotaIngreso", DbType.Int32,notaIngreso.IdNotaIngreso);
                        db.ExecuteNonQuery(cmdDeleteDetalle, transaction);

                        DbCommand cmdDetalle = db.GetStoredProcCommand("InsertDetalleNotaIngreso");
                        db.AddInParameter(cmdDetalle, "@cantidad", DbType.Decimal);
                        db.AddInParameter(cmdDetalle,"@precio", DbType.Currency);
                        db.AddInParameter(cmdDetalle,"@idBien", DbType.Int32);
                        db.AddInParameter(cmdDetalle,"@idNotaIngreso", DbType.Int32);

                        foreach (DetalleNotaIngreso d in notaIngreso.Detalle)
                        {
                            db.SetParameterValue(cmdDetalle,"@cantidad",d.Cantidad);
                            db.SetParameterValue(cmdDetalle, "@precio",d.Precio);
                            db.SetParameterValue(cmdDetalle, "@idBien", d.Bien.IdBien);
                            db.SetParameterValue(cmdDetalle, "@idNotaIngreso", notaIngreso.IdNotaIngreso);
                            db.ExecuteNonQuery(cmdDetalle,transaction);
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                }
            }
        }

        public void Eliminar(int idNotaIngreso)
        {
            DbCommand command = db.GetStoredProcCommand("DeleteNotaIngreso");
            db.AddInParameter(command, "idNotaIngreso", DbType.Int32, idNotaIngreso);
            db.ExecuteNonQuery(command);
        }

        public NotaIngreso ObtenerPorFactura(int idFactura)
        {
            NotaIngreso notaIngreso = null;
            
            try
            {
                DbCommand command = db.GetStoredProcCommand("GetNotaIngresaByFacturaID");
                db.AddInParameter(command, "idFactura", DbType.Int32, idFactura);
                IDataReader dr = db.ExecuteReader(command);
                while (dr.Read())
                {
                    notaIngreso = new NotaIngreso();
                    {
                        notaIngreso.IdNotaIngreso = dr.GetInt32(dr.GetOrdinal("IdNotaIngreso"));
                        notaIngreso.Descripcion = dr.GetString(dr.GetOrdinal("Descripcion"));
                        notaIngreso.Estado = dr.GetInt32(dr.GetOrdinal("Estado"));
                        notaIngreso.Fecha = dr.GetDateTime(dr.GetOrdinal("FechaIngreso"));
                        notaIngreso.Empleados = EmpleadoData.Instancia.ObtenerPorID(dr.GetInt16(dr.GetOrdinal("IDEmpleado")));
                        notaIngreso.Factura = FacturaData.Instancia.ObtenerPorID(dr.GetInt32(dr.GetOrdinal("IDFactura")));
                        notaIngreso.Detalle = DetalleNotaIngresoData.Instancia.ListarPorNotaIngreso(dr.GetInt32(dr.GetOrdinal("IdNotaIngreso")));
                    };
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return notaIngreso;
        }

        public List<int> ListarPorGuia(List<int> arreglo)
        {
            List<int> notaIngresoID = new List<int>();
            DbCommand command = db.GetStoredProcCommand("GetAllIdNotaIngreso");
            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    notaIngresoID.Add(dr.GetInt32(dr.GetOrdinal("IdNotaIngreso")));
                };
            }
            List<int> arregloID = new List<int>();
            if (arreglo.Count > 0)
            {
                foreach (var item in arreglo)
                {

                    foreach (var variable in notaIngresoID)
                    {
                        if (variable != item)
                        {
                            arregloID.Add(variable);
                        }
                    }
                }
                return arregloID;
            }
            else
            {
                return notaIngresoID;
            }
        }
    }
}
